-- ============================================================================
-- 宠物管理系统 - 宠物品种表 (PostgreSQL版本)
-- ============================================================================
-- 功能说明：
--   1. 存储各类宠物的品种信息
--   2. 提供品种特征、健康、护理等详细信息
--   3. 支持按宠物类型筛选和分页查询
--   4. 为用户选择宠物提供参考依据
-- ============================================================================

-- 创建宠物品种表
CREATE TABLE IF NOT EXISTS pet_breed (
    -- 主键ID，自增长
    id BIGSERIAL PRIMARY KEY,
    
    -- 品种名称，如"金毛寻回犬"、"英国短毛猫"
    name VARCHAR(100) NOT NULL,
    
    -- 宠物类型：dog-狗, cat-猫, bird-鸟, rabbit-兔, hamster-仓鼠等
    pet_type VARCHAR(50) NOT NULL,
    
    -- 品种描述，详细介绍该品种的来源、特点等
    description TEXT,
    
    -- 体型分类：small-小型, medium-中型, large-大型
    size VARCHAR(20),
    
    -- 性格特点，如"友善、活泼、忠诚"
    temperament VARCHAR(255),
    
    -- 平均寿命（年），如10-15年记录为12或13
    average_lifespan INTEGER,
    
    -- 常见健康问题，如"髋关节发育不良、癫痫"
    health_issues TEXT,
    
    -- 饲养难度等级（1-5）：1最容易，5最困难
    care_level INTEGER,
    
    -- 品种图片URL，用于展示品种外观
    image_url VARCHAR(500),
    
    -- 是否适合新手：true-适合, false-不适合
    suitable_for_beginners BOOLEAN DEFAULT true,
    
    -- 运动需求等级（1-5）：1最低，5最高
    exercise_needs INTEGER,
    
    -- 美容需求等级（1-5）：1最低（易打理），5最高（需要专业美容）
    grooming_needs INTEGER,
    
    -- 状态：0-禁用（不显示）, 1-启用（正常显示）
    status INTEGER NOT NULL DEFAULT 1,
    
    -- 创建时间
    create_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP,
    
    -- 更新时间
    update_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP
);

-- ============================================================================
-- 索引创建
-- ============================================================================

-- 宠物类型索引：按类型筛选品种（如只查询狗的品种）
CREATE INDEX IF NOT EXISTS idx_pet_breed_pet_type ON pet_breed(pet_type);

-- 状态索引：只查询启用的品种
CREATE INDEX IF NOT EXISTS idx_pet_breed_status ON pet_breed(status);

-- 品种名称索引：用于名称搜索
CREATE INDEX IF NOT EXISTS idx_pet_breed_name ON pet_breed(name);

-- 复合索引：类型+状态，优化常用查询
CREATE INDEX IF NOT EXISTS idx_pet_breed_type_status ON pet_breed(pet_type, status);

-- ============================================================================
-- 表和列注释
-- ============================================================================

COMMENT ON TABLE pet_breed IS '宠物品种表 - 存储各类宠物的详细品种信息';
COMMENT ON COLUMN pet_breed.id IS '主键ID';
COMMENT ON COLUMN pet_breed.name IS '品种名称';
COMMENT ON COLUMN pet_breed.pet_type IS '宠物类型（dog-狗, cat-猫, bird-鸟等）';
COMMENT ON COLUMN pet_breed.description IS '品种描述';
COMMENT ON COLUMN pet_breed.size IS '体型（small-小型, medium-中型, large-大型）';
COMMENT ON COLUMN pet_breed.temperament IS '性格特点';
COMMENT ON COLUMN pet_breed.average_lifespan IS '平均寿命（年）';
COMMENT ON COLUMN pet_breed.health_issues IS '常见健康问题';
COMMENT ON COLUMN pet_breed.care_level IS '饲养难度（1-5，1最容易）';
COMMENT ON COLUMN pet_breed.image_url IS '品种图片URL';
COMMENT ON COLUMN pet_breed.suitable_for_beginners IS '是否适合新手';
COMMENT ON COLUMN pet_breed.exercise_needs IS '运动需求（1-5，1最低）';
COMMENT ON COLUMN pet_breed.grooming_needs IS '美容需求（1-5，1最低）';
COMMENT ON COLUMN pet_breed.status IS '状态（0-禁用, 1-启用）';
COMMENT ON COLUMN pet_breed.create_time IS '创建时间';
COMMENT ON COLUMN pet_breed.update_time IS '更新时间';

-- ============================================================================
-- 触发器：自动更新update_time
-- ============================================================================

-- 创建触发器函数
CREATE OR REPLACE FUNCTION update_pet_breed_timestamp()
RETURNS TRIGGER AS $$
BEGIN
    NEW.update_time = CURRENT_TIMESTAMP;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- 创建触发器
DROP TRIGGER IF EXISTS trigger_update_pet_breed_timestamp ON pet_breed;
CREATE TRIGGER trigger_update_pet_breed_timestamp
    BEFORE UPDATE ON pet_breed
    FOR EACH ROW
    EXECUTE FUNCTION update_pet_breed_timestamp();

-- ============================================================================
-- 插入示例数据
-- ============================================================================
-- 以下为常见宠物品种的示例数据，可根据实际需求添加或修改

INSERT INTO pet_breed (name, pet_type, description, size, temperament, average_lifespan, 
                       health_issues, care_level, suitable_for_beginners, exercise_needs, 
                       grooming_needs, status) 
VALUES
-- 狗类品种
('金毛寻回犬', 'dog', 
 '金毛寻回犬是最常见的家犬之一，因为它很容易养，有耐心并且对主人要求不多，只要定期的运动，食物和兽医体检就可以了。金毛的独特之处在于它讨人喜欢的性格，是属于匀称、有力、活泼的一个犬种。', 
 'large', '友善、可靠、值得信赖', 12, 
 '髋关节发育不良、癫痫', 2, true, 4, 3, 1),

('泰迪犬', 'dog', 
 '贵宾犬，也称"贵妇犬"，又称"卷毛狗"，在德语中，Pudel是"水花飞溅"的意思，是犬亚科犬属的一种动物。泰迪犬其实是贵宾犬的一种美容造型。', 
 'small', '聪明、活泼、性情优良', 15, 
 '骨折、癫痫', 3, true, 3, 4, 1),

('哈士奇', 'dog', 
 '西伯利亚雪撬犬，常见别名哈士奇。俗名：二哈。西伯利亚雪撬犬体重介于25～60磅（16～27kg）之间，身高大约20～23.5英寸（51～60cm）。是一种中型犬。', 
 'large', '友好、外向、警觉', 12, 
 '髋关节发育不良、白内障', 3, false, 5, 3, 1),

-- 猫类品种
('英国短毛猫', 'cat', 
 '英国短毛猫，体形圆胖，四肢粗短发达，毛短而密，头大脸圆，温柔平静，对人友善，极易饲养。大而圆的眼睛根据被毛不同而呈现各种颜色。', 
 'medium', '平静、友善、易相处', 15, 
 '肥胖症、心脏病', 2, true, 2, 2, 1),

('波斯猫', 'cat', 
 '波斯猫是最常见的长毛猫，波斯猫有一张讨人喜爱的面庞，长而华丽的毛发，优雅的举止，故有"猫中王子"、"王妃"之称，是世界上爱猫者最喜欢的纯种猫之一。', 
 'medium', '温文尔雅、反应灵敏', 13, 
 '呼吸系统疾病、泪道阻塞', 4, false, 2, 5, 1),

-- 鸟类品种
('虎皮鹦鹉', 'bird', 
 '虎皮鹦鹉是鹦形目鹦鹉科的鸟类，又名娇凤，属小型攀禽品种，原产于澳大利亚的内陆地区。野生的虎皮鹦鹉栖息于林缘、草地等处。结群活动。以植物种子等为食。', 
 'small', '活泼好动、易于驯养', 8, 
 '呼吸道疾病', 2, true, 3, 1, 1);

-- ============================================================================
-- 使用说明
-- ============================================================================
-- 1. 执行本脚本创建表、索引和示例数据
-- 2. 可通过管理后台继续添加更多品种数据
-- 3. 前端可按pet_type筛选不同类型的宠物品种
-- 4. 可根据care_level、exercise_needs等帮助用户选择合适的宠物
-- 
-- 查询示例：
-- 查询所有狗的品种：
--   SELECT * FROM pet_breed WHERE pet_type = 'dog' AND status = 1;
-- 
-- 查询适合新手的品种：
--   SELECT * FROM pet_breed WHERE suitable_for_beginners = true AND status = 1;
-- 
-- 性能优化建议：
-- 1. 定期执行 VACUUM ANALYZE pet_breed; 优化表性能
-- 2. 如果图片使用外部URL，建议使用CDN加速
-- 3. 监控索引使用情况，必要时调整索引策略
-- ============================================================================
